﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using Oracle.DataAccess.Client;


namespace Twi.DataAccess.DBUtility
{
    public static class DBHelper
    {
        public static Dictionary<string, DbProviderFactory> _dicDbProviderFac = new Dictionary<string, DbProviderFactory>();

        public static XElement GetMultiple(string providerName, string connStr, string sql) 
        {
            if (!_dicDbProviderFac.ContainsKey(providerName)) 
            {
                _dicDbProviderFac.Add(providerName, DbProviderFactories.GetFactory(providerName));
            }
            return GetMultiple(_dicDbProviderFac[providerName], connStr, sql);
        }

        public static void Execute(string providerName, string connStr, string[] sqls) 
        {
            if (!_dicDbProviderFac.ContainsKey(providerName))
            {
                _dicDbProviderFac.Add(providerName, DbProviderFactories.GetFactory(providerName));
            }
            Execute(_dicDbProviderFac[providerName], connStr, sqls);
        }

        public static XElement GetMultiple(DbProviderFactory fac, string connStr, string sql) 
        {
            using (DbConnection conn = fac.CreateConnection())
            {
                conn.ConnectionString = connStr;
                conn.Open();
                TwiCommand cmd = DBHelper.CreateCommand(conn.CreateCommand());
                cmd.CommandText = sql;
                return cmd.GetMultiple();
            }
        }

        public static XElement GetMultiple(string sql)
        {
            using (DbConnection conn = TwiDbProviderFactory.Instance.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                conn.Open();
                TwiCommand cmd = DBHelper.CreateCommand(conn.CreateCommand());
                cmd.CommandText = sql;
                return cmd.GetMultiple();
            }
        }

        public static void Execute(string[] sqls) 
        {
            using (DbConnection conn = TwiDbProviderFactory.Instance.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                conn.Open();
                TwiCommand cmd = DBHelper.CreateCommand(conn.CreateCommand());
                foreach (string sql in sqls)
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static void Execute(DbProviderFactory fac, string connStr, string[] sqls) 
        {
            using (DbConnection conn = fac.CreateConnection())
            {
                conn.ConnectionString = connStr;
                conn.Open();
                TwiCommand cmd = DBHelper.CreateCommand(conn.CreateCommand());
                foreach (string sql in sqls) 
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 数据库连接
        /// </summary>
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;

        /// <summary>
        /// 数据库类型，默认SQLServer
        /// </summary>
        public static EnumDBType CurrentDBType = EnumDBType.SQLServer;

        /// <summary>
        /// 当前数据库参数前缀
        /// </summary>
        public static char ParamePrx = '@';

        /// <summary>
        /// 函数调用时的前缀
        /// </summary>
        public static string FunctionPrx = "dbo.";

        public static string FromDual = "";

        public static string StringConcat = "+";

        /// <summary>
        /// 
        /// </summary>
        static DBHelper()
        {
            if (ConfigurationManager.ConnectionStrings["SQLConnString"] != null)
            {
                switch (ConfigurationManager.ConnectionStrings["SQLConnString"].ProviderName)
                {
                    case "System.Data.SqlClient":
                        CurrentDBType = EnumDBType.SQLServer;
                        ParamePrx = '@';
                        FunctionPrx = "dbo.";
                        FromDual = "";
                        break;
                    case "Oracle.DataAccess.Client":
                        CurrentDBType = EnumDBType.Oracle;
                        ParamePrx = ':';
                        FunctionPrx = "";
                        FromDual = " FROM DUAL";
                        StringConcat = "||";
                        break;
                }
            }
        }

        public static DbParameter ToParameter(string paraName, object paraValue) 
        {
            OracleParameter para = null;
            if (paraValue is XElement)
            {
                para = new OracleParameter(paraName, OracleDbType.XmlType);
            }
            else if (paraValue is DateTime)
            {
                para = new OracleParameter(paraName, OracleDbType.Date);
            }
            else if (paraValue is Guid)
            {
                para = new OracleParameter(paraName, OracleDbType.Raw);
                paraValue = ((Guid)paraValue).ToByteArray();
            }
            else
            {
                para = new OracleParameter();
                para.ParameterName = paraName;
            }
            para.Value = paraValue;
            return para;
        }

        public static DbParameter ToParameter<T>(this XElement me) 
        {
            if (CurrentDBType == EnumDBType.SQLServer)
            {
                return me.ToSqlParameter<T>() as DbParameter;
            }
            else
            {
                if (typeof(T) == typeof(XElement)) 
                {
                    return ToParameter(me.Name.LocalName, me);
                }
                else if (typeof(T) == typeof(DateTime))
                {
                    return ToParameter(me.Name.LocalName, DateTime.Parse(me.Value));
                }
                else if (typeof(T) == typeof(Guid))
                {
                    return ToParameter(me.Name.LocalName, new Guid(me.Value));
                }
                else 
                {
                    return ToParameter(me.Name.LocalName, me.Value);
                }
            }
        }

        public static TwiCommand CreateCommand() 
        {
            if (CurrentDBType == EnumDBType.SQLServer)
            {
                return new TwiCommandSql();
            }
            else
            {
                return new TwiCommandOracle();
            }
        }

        public static TwiCommand CreateCommand(DbCommand cmd)
        {
            if (cmd is SqlCommand)
            {
                return new TwiCommandSql(cmd);
            }
            else
            {
                return new TwiCommandOracle(cmd);
            }
        }

        public static TwiCommand CreateCommand(DbConnection conn, DbTransaction txn)
        {
            if (CurrentDBType == EnumDBType.SQLServer)
            {
                return new TwiCommandSql(conn, txn);
            }
            else
            {
                return new TwiCommandOracle(conn, txn);
            }
        }

        #region 获取语句中的参数
        /// <summary>
        ///  获取语句中的参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<string> GetParameterNames(this string sql)
        {
            sql = sql + " "; //防止该语句已参数结尾
            List<string> retList = new List<string>();
            bool hasUnmatchedSingleQuotation = false; //是否存在未配对的单引号
            int curParamStartAt = -1;//当前参数在sql中的开始位置，即冒号后一位
            int curPos = 1;
            do
            {
                char curChar = sql[curPos];
                curPos++;
                if (curChar == '\'')
                {
                    hasUnmatchedSingleQuotation = !hasUnmatchedSingleQuotation;
                    continue;
                }
                if (!hasUnmatchedSingleQuotation)//防止在字符窜里面仍去判断参数
                {
                    if (curChar == ParamePrx)
                    {
                        curParamStartAt = curPos;
                    }
                    else if (curParamStartAt != -1 &&
                        (curChar < 'a' || curChar > 'z') && (curChar < 'A' || curChar > 'Z') && (curChar < '0' || curChar > '9') && curChar != '_')
                    {
                        string paramName = sql.Substring(curParamStartAt, curPos - curParamStartAt - 1).ToUpper();
                        if (!retList.Contains(paramName) || CurrentDBType == EnumDBType.Oracle)
                        {
                            retList.Add(paramName);
                        }

                        curParamStartAt = -1;
                    }
                }
            }
            while (curPos < sql.Length);
            return retList;
        }
        #endregion

        #region 准备执行语句前准备抽取OracleCommand参数
        /// <summary>
        /// 准备执行语句前准备抽取OracleCommand参数
        /// </summary>
        /// <param name="me"></param>
        /// <param name="maximumOrclParameters"></param>
        public static void PrepareOracleParameters(this Oracle.DataAccess.Client.OracleCommand me, List<Oracle.DataAccess.Client.OracleParameter> maximumOrclParameters)
        {
            var paramNameList = me.CommandText.GetParameterNames();
            me.Parameters.Clear();
            foreach (string paramName in paramNameList)
            {
                var p = maximumOrclParameters.SingleOrDefault(x => x.ParameterName.ToUpper() == paramName.ToUpper());
                if (p != null)
                {
                    p = (Oracle.DataAccess.Client.OracleParameter)p.Clone();
                    p.ParameterName = p.ParameterName + me.Parameters.Count.ToString("000");
                    me.Parameters.Add(p);
                }
            }
        }
        #endregion

        #region 获得DataColumn简析出XML结构
        /// <summary>
        /// 获得DataColumn简析出XML结构
        /// </summary>
        /// <param name="dataColumn"></param>
        /// <param name="value"></param>
        /// <param name="isXml"></param>
        /// <returns></returns>
        public static XElement ToXElement(this DataColumn dataColumn, object value, bool isXml = false)
        {
            XElement xe = new XElement(GetNodeName(dataColumn.ColumnName));
            if (value == DBNull.Value)
            {
                return xe;
            }
            if (isXml)
            {
                xe.Add(XElement.Parse(string.Format("<root>{0}</root>", value)).Elements().ToArray());
            }
            else
            {
                xe.SetValue(value.ToString().ReplaceLowOrderASCIICharacters());
            }
            return xe;
        }
        #endregion

        public static bool CompareConnStr(DbConnection conn, string connStr2) 
        {
            string connStrNoPwd = conn.ConnectionString;
            var parts1 = connStrNoPwd.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries).ToList();
            var parts2 = connStr2.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries).ToList();
            if (parts2.Count != parts1.Count + 1) { return false; }
            parts2.Remove(parts2.Single(x => x.Trim().ToLower().StartsWith("password")));
            return parts1.SequenceEqual(parts2); 
        }


        #region 获得DbDataReader简析出XML结构
        /// <summary>
        /// 获得DbDataReader简析出XML结构
        /// </summary>
        /// <param name="rdr"></param>
        /// <param name="i"></param>
        /// <param name="isXml"></param>
        /// <returns></returns>
        public static XElement ToXElement(this DbDataReader rdr, DbCommand cmd, int i, bool isXml = false)
        {
            string name = rdr.GetName(i);
            if (!(cmd is SqlCommand) && CompareConnStr(cmd.Connection, ConnectionString))
            {
                name = GetNodeName(rdr.GetName(i));
            }
            if (rdr.GetValue(i) == DBNull.Value)
            {
                XElement xe = new XElement(name);
                return xe;
            }
            if (isXml)
            {
                return GetXElementGenerically(rdr, i);
            }
            else if (rdr.GetDataTypeName(i).ToUpper() == "RAW")
            {
                XElement xe = new XElement(name);
                xe.SetValue(new Guid(rdr.GetValue(i) as byte[]));
                return xe;
            }
            else
            {
                XElement xe = new XElement(name);
                xe.SetValue(rdr.GetValue(i).ToString().ReplaceLowOrderASCIICharacters());
                return xe;
            }
        }


        #endregion

        /// <summary>
        /// 获取Guid类型值的公用方法
        /// </summary>
        /// <param name="rdr"></param>
        /// <param name="ordinal"></param>
        /// <returns></returns>
        public static Guid GetGuidGenerically(this DbDataReader rdr, int ordinal) 
        {
            if (CurrentDBType == EnumDBType.Oracle)
            {
                return new Guid(rdr.GetValue(ordinal) as byte[]);
            }
            else
            {
                return rdr.GetGuid(ordinal);
            }
        }

        public static Guid GetGuidGenerically(this TwiCommand cmd)
        {
            var ret = cmd.ExecuteScalar();
            if (ret == null) { return Guid.Empty; }
            if (CurrentDBType == EnumDBType.Oracle)
            {
                return new Guid(ret as byte[]);
            }
            else
            {
                return (Guid)ret;
            }
        }

        public static string ReplaceLowOrderASCIICharacters(this string tmp)
        {
            StringBuilder info = new StringBuilder();
            foreach (char cc in tmp)
            {
                int ss = (int)cc;
                if (((ss >= 0) && (ss <= 8)) || ((ss >= 11) && (ss <= 12)) || ((ss >= 14) && (ss <= 32)))
                    info.AppendFormat(" ", ss);
                else info.Append(cc);
            }
            return info.ToString();
        }

        public static XElement GetXElementGenerically(this DbDataReader rdr, int ordinal)
        {
            if (CurrentDBType == EnumDBType.Oracle)
            {
                return new XElement(rdr.GetName(ordinal), XElement.Parse(rdr.GetString(ordinal)).Elements());
            }
            else
            {
                try
                {                    
                    return XElement.Parse(string.Format("<{0}>",rdr.GetName(ordinal))  + rdr.GetString(ordinal) + string.Format("</{0}>", rdr.GetName(ordinal)));
                }
                catch 
                {
                    return null;
                }
            }
        }


        #region 获得XML节点名称
        /// <summary>
        /// 获得XML节点名称(传入大写名称，获得数据库设计时常规名称)
        /// </summary>
        /// <param name="upperColumnName">大写名称</param>
        /// <returns></returns>
        public static string GetNodeName(string upperColumnName)
        {
            if (DBHelper.CurrentDBType == EnumDBType.SQLServer) return upperColumnName;

            string nodeName = "";

            try
            {
                DataRow dr = DBKeyword.DtKeyword.Select(string.Format("UPPTBLCOL='{0}'", upperColumnName.ToUpper())).FirstOrDefault();
                if (dr != null)
                {
                    nodeName = dr["TBLCOL"].ToString();
                }
                else
                {
                    nodeName = upperColumnName;
                }
            }
            catch
            {
                nodeName = upperColumnName;
            }

            return nodeName;
        }
        #endregion

        #region 获取并缓存数据库表列的元数据信息

        private static DataSet _metaDataSet = new DataSet();

        /// <summary>
        /// 获取并缓存数据库表列的元数据信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataTable GetMetaDataTable(string tableName)
        {
            if (CurrentDBType == EnumDBType.SQLServer)
            {
                return GetMetaDataTableForSQLServer(tableName);
            }
            else
            {
                return GetMetaDataTableForOracle(tableName);
            }
 
        }



        public static DataTable GetMetaDataTableForSQLServer(string tableName)
        {
            if (!_metaDataSet.Tables.Contains(tableName))
            {
                DbConnection conn = TwiDbProviderFactory.Instance.CreateConnection();
                conn.ConnectionString = DBHelper.ConnectionString;
                conn.Open();
                DbCommand _cmd = conn.CreateCommand();

                    #region SQL Server
                    _cmd.CommandText = string.Format(@"SELECT  
  表名 = case when a.colorder=1 then d.name else '' end,
  字段序号 = a.colorder,
  字段名 = a.name,
  标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end,
  主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
  SELECT name FROM sysindexes WHERE indid in(
  SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '1' else '0' end,
  类型 = b.name,
  占用字节数 = a.length,
  长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
  小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
  允许空 = case when a.isnullable=1 then '1'else '0' end,
  默认值 = isnull(e.text,''),
  计算列 = iscomputed
FROM  syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
where  d.name='{0}'
order by  a.id,a.colorder", tableName);
                    SqlDataAdapter sa = new SqlDataAdapter(_cmd as SqlCommand);
                    sa.Fill(_metaDataSet, tableName);
                    #endregion

            }
            return _metaDataSet.Tables[tableName];
        }


        public static DataTable GetMetaDataTableForOracle(string tableName)
        {
            if (!_metaDataSet.Tables.Contains(tableName))
            {
                DbConnection conn = TwiDbProviderFactory.Instance.CreateConnection();
                conn.ConnectionString = DBHelper.ConnectionString;
                conn.Open();
                DbCommand _cmd = conn.CreateCommand();

             
                    #region Oracle
                    _cmd.CommandText = string.Format(@"select TABLE_NAME as ""表名"",
       column_id as  ""字段序号"",
        column_name as ""字段名"",
      case when column_name in (select cu.column_name from user_cons_columns cu, user_constraints au 
where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name =user_tab_columns.TABLE_NAME) then '1' else '0' end as  ""主键"",
       DATA_TYPE as ""类型"",
       DATA_LENGTH as ""长度"",
       case when NULLABLE='Y'then '1'else '0'end as ""允许空"",
       DATA_DEFAULT as ""默认值""
from user_tab_columns where TABLE_NAME='{0}'", tableName.ToUpper());

                    var sa = new Oracle.DataAccess.Client.OracleDataAdapter(_cmd as Oracle.DataAccess.Client.OracleCommand);
                    sa.Fill(_metaDataSet, tableName);
                    foreach (DataRow dr in _metaDataSet.Tables[tableName.ToUpper()].Rows)
                    {
                        if (dr["默认值"] != DBNull.Value)
                        {
                            StringBuilder strBuilder = new StringBuilder();
                            strBuilder.Append(dr["默认值"]);
                        }
                    }
                    #endregion
                
            }
            return _metaDataSet.Tables[tableName];
        }

        #endregion

        #region 获取元数据—主键
        /// <summary>
        /// 获取元数据—主键
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string[] GetMetaDataTablePrimaryKeys(string tableName)
        {
            return DBHelper.GetMetaDataTable(tableName).Rows.OfType<DataRow>().Where(x => x["主键"].ToString() == "1").Select(x => x["字段名"].ToString()).ToArray();
        }
        #endregion 

        #region XML To Parameter

        #region SqlParameter
        /// <summary>
        /// SqlParameter
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="me"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static SqlParameter ToSqlParameter<T>(this XElement me, string parameterName = "")
        {
            if (me == null)
            {
                return null;
            }
            if (parameterName == "")
            {
                parameterName = me.Name.LocalName;
            }
            //特殊字段的处理
            if (me.Name.LocalName == "SortOrder" && me.Value.ToString().Trim() == "")
            {
                SqlParameter p = new SqlParameter("@SortOrder", SqlDbType.Int);
                p.Value = 0;
                return p;
            }

            if (typeof(T) == typeof(XElement))
            {
                SqlParameter p = new SqlParameter("@" + parameterName, SqlDbType.Xml);
                p.Value = me.HasElements ? (object)new System.Xml.XmlTextReader(me.Elements().Select(x => x.ToString()).Aggregate((x, y) => x + y), System.Xml.XmlNodeType.Element, null)
                    : DBNull.Value;
                return p;
            }
            string value = me.Value.Trim();
            if (me.HasElements) 
            {
                if (me.Element("NewValue") == null)
                {
                    value = me.ToString();
                }
                else
                {
                    value = me.Element("NewValue").Value;  
                }
            }            
            if (typeof(T) == typeof(string))
            {
                return new SqlParameter("@" + parameterName, value);
            }
            else if (typeof(T) == typeof(DateTime))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : DateTime.Parse(value));
            }
            else if (typeof(T) == typeof(Guid))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : new Guid(value));
            }
            else if (typeof(T) == typeof(decimal))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : decimal.Parse(value));
            }
            else if (typeof(T) == typeof(float))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : float.Parse(value));
            }
            else if (typeof(T) == typeof(double))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : double.Parse(value));
            }
            else if (typeof(T) == typeof(int))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : int.Parse(value));
            }
            else if (typeof(T) == typeof(long))
            {
                return new SqlParameter("@" + parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : long.Parse(value));
            }

            return null;
        }
        #endregion

        #region OracleParameter
        /// <summary>
        /// OracleParameter
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="me"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public static Oracle.DataAccess.Client.OracleParameter ToOracleParameter<T>(this XElement me, string parameterName = "")
        {
            if (me == null)
            {
                return null;
            }
            if (parameterName == "")
            {
                parameterName = me.Name.LocalName;
            }
            //特殊字段的处理
            if (me.Name.LocalName == "SortOrder" && me.Value.ToString().Trim() == "")
            {
                Oracle.DataAccess.Client.OracleParameter p = new Oracle.DataAccess.Client.OracleParameter("SortOrder", SqlDbType.Int);
                p.Value = 0;
                return p;
            }

            if (typeof(T) == typeof(XElement))
            {
                Oracle.DataAccess.Client.OracleParameter p = new Oracle.DataAccess.Client.OracleParameter(parameterName, SqlDbType.Xml);
                p.Value = me.HasElements ? (object)new System.Xml.XmlTextReader(me.Elements().Select(x => x.ToString()).Aggregate((x, y) => x + y), System.Xml.XmlNodeType.Element, null)
                    : DBNull.Value;
                return p;
            }

            string value = me.Element("NewValue") == null ? (me.Value) : me.Element("NewValue").Value;
            if (typeof(T) == typeof(string))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, value);
            }
            else if (typeof(T) == typeof(DateTime))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : DateTime.Parse(value));
            }
            else if (typeof(T) == typeof(Guid))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : new Guid(value).ToByteArray());
            }
            else if (typeof(T) == typeof(decimal))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : decimal.Parse(value));
            }
            else if (typeof(T) == typeof(float))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : float.Parse(value));
            }
            else if (typeof(T) == typeof(double))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : double.Parse(value));
            }
            else if (typeof(T) == typeof(int))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : int.Parse(value));
            }
            else if (typeof(T) == typeof(long))
            {
                return new Oracle.DataAccess.Client.OracleParameter(parameterName, string.IsNullOrEmpty(value) ? (object)DBNull.Value : long.Parse(value));
            }

            return null;
        }
        #endregion

        #region SetOracleParameter

        public static void SetOracleParameter(this Oracle.DataAccess.Client.OracleCommand me, string paraName, object paraValue)
        {
            if (paraValue is XElement)
            {
                paraValue = new Oracle.DataAccess.Types.OracleXmlType(me.Connection, new XmlTextReader(paraValue.ToString(), XmlNodeType.Document, null));
            }
            else if (paraValue is IEnumerable<XElement>)
            {
                paraValue = (paraValue as IEnumerable<XElement>).Select(x => x.ToString()).Aggregate((x, y) => x + y);
            }

            if (me.Parameters.Contains(paraName))
            {
                me.Parameters[paraName].Value = paraValue;
            }
            else
            {
                var para = new Oracle.DataAccess.Client.OracleParameter(paraName, paraValue);
                if (paraValue is XElement)
                {
                    para.DbType = DbType.Xml;
                }
                me.Parameters.Add(para);
            }
        }

        public static void SetOracleParameter(this Oracle.DataAccess.Client.OracleCommand me, Oracle.DataAccess.Client.OracleParameter para)
        {
            if (para == null) { return; }
            if (me.Parameters.Contains(para.ParameterName))
            {
                me.Parameters[para.ParameterName].Value = para.Value;
            }
            else
            {
                me.Parameters.Add(para);
            }
        }

        public static void SetOracleParameters(this Oracle.DataAccess.Client.OracleCommand me, Oracle.DataAccess.Client.OracleParameter[] parameters)
        {
            foreach (var x in parameters)
            {
                me.SetOracleParameter(((ICloneable)x).Clone() as Oracle.DataAccess.Client.OracleParameter);
            }
        }

        #endregion

        #endregion


        #region 设置Command事务和连接为当前事物和当前事务的连接
        /// <summary>
        /// 设置Command事务和连接为当前事物和当前事务的连接
        /// </summary>
        /// <param name="me"></param>
        /// <param name="txn"></param>
        public static void SetTransactionWithConnection(this DbCommand me, DbTransaction txn)
        {
            me.Transaction = txn;
            me.Connection = txn.Connection;
            me.CommandTimeout = 120;
        }
        #endregion


    }

    public enum EnumDBType
    {
        SQLServer = 1,
        Oracle = 2,
    }

    public enum GSDbType
    {
        SqlBigInt = 0,
        SqlBit = 2,
        SqlNVarChar = 12,
        SqlVarChar = 22,
        OracleInt16 = 111,
        OracleInt32 = 112,
        OracleInt64 = 113,
        OracleNClob = 116,
        OracleNChar = 117,
        OracleNVarchar2 = 119,
        OracleVarchar2 = 126,
    }
}
